﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.IO;
using System.Data.SqlClient;
using BookLibrary.MyDataSetTableAdapters;

namespace BookLibrary
{
    public sealed class Database
    {
        private static volatile Database db;
        private static Object sleutel = new Object();

        public MyDataSet.BookDataTable bookTable;
        public MyDataSet.CategorieDataTable categorieTable;
        public MyDataSet.UitgeverDataTable uitgeverTable;
        public MyDataSet.KlaslijstDataTable klaslijstTable;
        public MyDataSet.Klaslijst_BookDataTable klas_bookTable;

        public MyDataSet dataSet;
        private String connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=" + System.Environment.CurrentDirectory + "\\MyDatabase.mdf;Integrated Security=True;User Instance=True";

        public CategorieTableAdapter categorieAdapt;
        public Klaslijst_BookTableAdapter klas_bookAdapt;
        public UitgeverTableAdapter uitgeverAdapt;
        public KlaslijstTableAdapter klaslijstAdapt;
        public BookTableAdapter bookAdapt;

        private List<Categorie> _categories;
        private List<Uitgever> _publishers;
        private List<Book> _books;

        public IEnumerable<MyDataSet.BookRow> ApplyFilter()
        {
            var query = from b in bookTable
                        select b;
            return query;
        }

        private void MaakDbLeeg()
        {
            klas_bookTable.Rows.Clear();
            klaslijstTable.Rows.Clear();
            bookTable.Rows.Clear();
            uitgeverTable.Rows.Clear();
            categorieTable.Rows.Clear();
        }

        private Database()
        {
            init();
        }

        public static Database Instance
        {
            get
            {
                if (db == null)
                {
                    lock (sleutel)
                    {
                        if (db == null)
                            db = new Database();
                    }
                }

                return db;
            }
        }

        //Inladen van database uit de textfiles en deze aan een dataset koppelen.
        public void init()
        {
            _categories = new List<Categorie>();
            _publishers = new List<Uitgever>();
            _books = new List<Book>();

            LoadCategoriesFromText("C:\\DotNet\\categorie.txt");
            LoadPublishersFromText("C:\\DotNet\\uitgever.txt");
            LoadBooksFromText("C:\\DotNet\\boeken.txt");
            //MaakDbLeeg();
            //LoadFromDatabase();

            dataSet = new MyDataSet();

            bookTable = dataSet.Book;
            bookAdapt = new BookLibrary.MyDataSetTableAdapters.BookTableAdapter();
            bookAdapt.Fill(bookTable);

            //Haal data op uit DB voor Categorie Tabel
            categorieTable = dataSet.Categorie;
            categorieAdapt = new BookLibrary.MyDataSetTableAdapters.CategorieTableAdapter();
            categorieAdapt.Fill(categorieTable);

            uitgeverTable = dataSet.Uitgever;
            uitgeverAdapt = new BookLibrary.MyDataSetTableAdapters.UitgeverTableAdapter();
            uitgeverAdapt.Fill(uitgeverTable);

            klaslijstTable = dataSet.Klaslijst;
            klaslijstAdapt = new MyDataSetTableAdapters.KlaslijstTableAdapter();
            klaslijstAdapt.Fill(klaslijstTable);

            klas_bookTable = dataSet.Klaslijst_Book;
            klas_bookAdapt = new MyDataSetTableAdapters.Klaslijst_BookTableAdapter();
            klas_bookAdapt.ClearBeforeFill = true;
            klas_bookAdapt.Fill(klas_bookTable);
        }

        public List<Book> Books
        {
            get
            {
                return _books;
            }
        }

        public Uitgever[] Publishers
        {
            get
            {
                return _publishers.ToArray();
            }
        }

        public Categorie[] Categories
        {
            get
            {
                return _categories.ToArray();
            }
        }

        private void LoadFromDatabase()
        {

            String query = "INSERT INTO Categorie (Id, Description) VALUES ";
            foreach (var c in _categories)
            {
                query += "('" + c.Id + "', '" + c.Description + "'), ";
            }
            query = query.Substring(0, query.Length - 2);
            query += ";";
            DoQueryToDB(new SqlCommand(query));

            query = "INSERT INTO Uitgever (Id, Description) VALUES ";
            foreach (var u in _publishers)
            {
                query += "('" + u.Id + "', '" + u.Description + "'), ";
            }
            query = query.Substring(0, query.Length - 2);
            query += ";";
            DoQueryToDB(new SqlCommand(query));

            SqlCommand cmd;
            for (int i = 0; i < _books.Count; i++)
            {
                cmd = new SqlCommand("INSERT INTO Book (Titel, Isbn, Status, Prijs, CategorieId, UitgeverId) VALUES (@Titel, @Isbn, @Status, @Prijs, @CId, @UId);");
                cmd.Parameters.AddWithValue("@Titel", _books[i].Titel);
                cmd.Parameters.AddWithValue("@Isbn", _books[i].Isbn);
                cmd.Parameters.AddWithValue("@Status", _books[i].Status);
                cmd.Parameters.AddWithValue("@Prijs", _books[i].Prijs);
                cmd.Parameters.AddWithValue("@CId", _books[i].Categorie);
                cmd.Parameters.AddWithValue("@UId", _books[i].Uitgever);
                DoQueryToDB(cmd);
            }
        }

        public void LoadCategoriesFromText(string categoriesPath)
        {
            String[] items;
            Categorie c;

            foreach (String line in File.ReadAllLines(categoriesPath))
            {
                items = line.Split(new char[] { '\t' }, StringSplitOptions.RemoveEmptyEntries);
                if (items.Length == 2)
                {
                    c = new Categorie();
                    c.Id = items[0].Trim().ToUpper();
                    c.Description = items[1].Trim();
                    _categories.Add(c);
                }
            }
        }

        public void LoadPublishersFromText(string publishersPath)
        {
            String[] items;
            Uitgever u;

            foreach (String line in File.ReadAllLines(publishersPath))
            {
                items = line.Split(new char[] { '\t' }, StringSplitOptions.RemoveEmptyEntries);
                if (items.Length == 2)
                {
                    u = new Uitgever();
                    u.Id = items[0].Trim().ToUpper();
                    u.Description = items[1].Trim();
                    _publishers.Add(u);
                }
            }
        }

        public void LoadBooksFromText(string booksPath)
        {
            String[] items;
            Book book;
            try
            {
                String titel;
                String isbn;
                decimal prijs;
                int status;
                String uitgeverId;
                String categorieId;
                foreach (String line in File.ReadAllLines(booksPath))
                {
                    items = line.Split(new char[] { '\t' }, StringSplitOptions.RemoveEmptyEntries);
                    if (items.Length == 6)
                    {
                        titel = items[0].Trim();
                        isbn = items[1].Trim();
                        prijs = decimal.Parse(items[3].Trim());
                        status = int.Parse(items[4].Trim());
                        uitgeverId = items[5].Trim().ToUpper();
                        categorieId = items[2].Trim().ToUpper();

                        book = new Book(titel, isbn, prijs, status, uitgeverId, categorieId);
                        _books.Add(book);

                    }
                }
            }
            catch (KeyNotFoundException)
            {
                Console.Out.WriteLine("Could not find book. ");
            }
            catch (IOException)
            {
                Console.Out.WriteLine("Could not open or find the file " + booksPath + ", it might be in use.");
            }
        }

        //Verzend plaintext SQL queries naar de database. Word enkel gebruikt bij het aanmaken. Daarna is dit sterk af te raden icm datasets!
        private void DoQueryToDB(SqlCommand command)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlDataReader rdr = null;
            try
            {
                conn.Open();
                command.Connection = conn;
                //SqlCommand cmd = new SqlCommand(q, conn);
                rdr = command.ExecuteReader();
            }
            catch (Exception e)
            {
                //System.Windows.Forms.MessageBox.Show(e.Message);
            }
            finally
            {
                if (rdr != null)
                {
                    rdr.Close();
                }
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }

        //Sla de klassenlijsten op
        public void PersistKlasLijst()
        {
            klas_bookAdapt.Update(klas_bookTable);
        }

        public void Annuleer()
        {
            klas_bookAdapt.Fill(klas_bookTable);
        }


        public Dictionary<string, int> OverZichtCategorie()
        {
            Dictionary<string, int> categories = new Dictionary<string, int>();
            //SELECT c.Description, count(c.Id) AS aantal FROM Categorie c INNER JOIN Book b ON b.CategorieId = c.Id GROUP BY categorie ORDER BY categorie
            var query = from c in categorieTable
                        join b in bookTable on c.Id equals b.CategorieId
                        group c by c.Description into cat
                        select cat;

            foreach (var item in query)
            {
                categories.Add(item.Key, item.ToList().Count);
            }

            return categories;
        }

        public Dictionary<string, double> OverzichtBoekenlijst()
        {
            //SELECT kl.KlaslijstId, sum(b.Prijs) AS totale_prijs FROM Klaslijst_book kl INNER JOIN Book b ON kl.BookId = b.Isbn GROUP BY kl.KlaslijstId ORDER BY kl.KlasLijstId
            var query = from kl in klas_bookTable
                        join b in bookTable on kl.BookId equals b.Isbn
                        group kl by kl.KlaslijstId into id
                        select id;

            Dictionary<string, double> booklist = new Dictionary<string, double>();
            foreach (var item in query)
            {
                double prijs = 0;
                for (int i = 0; i < item.ToList().Count; i++)
                {
                    prijs += Double.Parse(item.ToList().ElementAt(i).BookRow.Prijs.ToString());

                }
                booklist.Add(item.Key, prijs);
            }

            return booklist;
        }

        public Dictionary<string, int> OverzichtBestellen()
        {
            //SELECT b.Titel, count(kl.BookId) AS aantal FROM KlasLijst_Book kl INNER JOIN Book b ON kl.BookId = b.Isbn GROUP BY b.Titel ORDER BY b.Titel
            var query = from kl in klas_bookTable
                        join b in bookTable on kl.BookId equals b.Isbn
                        group b by b.Titel into books
                        select books;

            Dictionary<string, int> bookList = new Dictionary<string, int>();
            foreach (var item in query)
            {
                bookList.Add(item.Key, item.ToList().Count());
            }
            return bookList;
        }

        public void UpdateClassList()
        {
            klas_bookAdapt.Update(klas_bookTable);
            klaslijstAdapt.Update(klaslijstTable);
        }
    }
}
